<?php
/*
 * @Author: your name
 * @Date: 2021-11-03 15:16:15
 * @LastEditTime: 2021-12-21 12:07:31
 * @LastEditors: Please set LastEditors
 * @Description: 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 * @FilePath: \property\app\Models\admin_model\Column.php
 */

namespace App\Models\admin_model;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Housing extends Model
{
    use HasFactory;
    static string $tableName = 'housing';

    public static function list($whereStr = '', $limitStr = '',$field='*')
    {
        return DB::select(' SELECT `housing`.*,`tower_building`.`title` as `building_title`,`tower_building`.`address` as `building_address`,`admins`.`nickName`,`admins`.`realName` FROM ' . self::$tableName . ' LEFT JOIN `admins` ON `admins`.`id` = `housing`.`admin_id` LEFT JOIN `tower_building` ON `tower_building`.`id` = `housing`.`building_id`     WHERE  `housing`.`delete_time` is null ' . $whereStr . ' ORDER BY `top_time` DESC,`sort` DESC , `id` DESC  ' . $limitStr);
    }
    
    public static function count($whereStr = '')
    {
        return DB::select(' SELECT COUNT(*) as `count` FROM ' . self::$tableName . '   WHERE  `delete_time` is null ' . $whereStr );
    }

    public static function housingAdminSelect(){
        return DB::select(' SELECT admins.`id` as `value` ,  CASE WHEN  admins.`realName` != "" THEN  concat(admins.`nickName`,"(",admins.`realName`,")") ELSE admins.`nickName` END as `title`  FROM `housing` INNER JOIN `admins` ON `admins`.`id` = `housing`.`admin_id` WHERE `housing`.`delete_time` is null  GROUP BY `admins`.`id` ORDER BY `admins`.`top_time` DESC, `admins`.`id` DESC        ');
    }
    

    public static function housingTagList(){
        // echo " SELECT GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(`tagList`,",", b.help_topic_id+1),",",-1) ) AS ".self::$tableName."  FROM `housing` JOIN mysql.help_topic b ON b.help_topic_id < ( length( housing.tagList ) - length( REPLACE ( housing.tagList, ',', '' )) + 1 )  WHERE `housing`.`delete_time` is null  ";
      
        return DB::select(' SELECT GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(`tagList`,",", b.help_topic_id+1),",",-1) ) AS `tagList`  FROM '.self::$tableName.' JOIN mysql.help_topic b ON b.help_topic_id < ( length( housing.tagList ) - length( REPLACE ( housing.tagList, ",", "" )) + 1 )  WHERE `housing`.`delete_time` is null  ');
    }

    public static function housingWebList($whereStr = '', $limitStr = ''){
        // echo '  SELECT  `housing`.*,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name`,`building`.`title` as `building_title` FROM  housing as `housing` LEFT JOIN `tower_building` as `building` ON `building`.`id` = `housing`.`building_id` LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id`   WHERE `housing`.`delete_time` is null AND `housing`.`shelves` = 1    '.$whereStr.' ORDER BY `housing`.`top_time` DESC , `housing`.`sort` DESC , `housing`.`id` DESC  '.$limitStr;
        return DB::select('  SELECT  `housing`.*,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name`,`building`.`title` as `building_title` FROM  housing as `housing` LEFT JOIN `tower_building` as `building` ON `building`.`id` = `housing`.`building_id` LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id`   WHERE `housing`.`delete_time` is null AND `housing`.`shelves` = 1    '.$whereStr.' ORDER BY `housing`.`top_time` DESC , `housing`.`sort` DESC , `housing`.`id` DESC  '.$limitStr);
    }
    

    public static function housingWebSelOne($id){
       return DB::select("SELECT  `housing`.*,`areaCommunity`.`name` as `community_name`,`admins`.`nickName` as `admin_nickName`,`admins`.`realName` as `admin_realName`,`areaStreet`.`name` as `street_name`,`building`.`title` as `building_title`,`building`.`address` as `building_address`,`building`.`latitude` as `building_latitude` , `building`.`longitude` as `building_longitude` FROM  housing as `housing` LEFT JOIN `tower_building` as `building` ON `building`.`id` = `housing`.`building_id` LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id` LEFT JOIN `admins` ON `admins`.`id` = `housing`.`admin_id`   WHERE `housing`.`id` =  ".$id." LIMIT 1" );
    }

    public static function housingWebSelOneCollect($id,$openid){
        // echo "SELECT  `housing`.*,CASE WHEN `collection`.`id` IS NOT NULL THEN 1 ELSE 0 END as `collection`,`areaCommunity`.`name` as `community_name`,`admins`.`nickName` as `admin_nickName`,`admins`.`realName` as `admin_realName`,`areaStreet`.`name` as `street_name`,`building`.`title` as `building_title`,`building`.`address` as `building_address`,`building`.`latitude` as `building_latitude` , `building`.`longitude` as `building_longitude` FROM  housing as `housing` LEFT JOIN `tower_building` as `building` ON `building`.`id` = `housing`.`building_id` LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id` LEFT JOIN `admins` ON `admins`.`id` = `housing`.`admin_id` LEFT JOIN `find_shop_collection_history` as `collection` ON  `collection`.`user_openid` = '".$openid."' AND `type` = 2 AND `collection`.`table` = 'housing' AND `collect`.`target_id` = `housing`.`id`   WHERE `housing`.`id` =  ".$id." LIMIT 1";
    // echo "SELECT  `housing`.*,CASE WHEN `collection`.`id` IS NOT NULL THEN 1 ELSE 0 END as `collection`,`areaCommunity`.`name` as `community_name`,`admins`.`nickName` as `admin_nickName`,`admins`.`realName` as `admin_realName`,`areaStreet`.`name` as `street_name`,`building`.`title` as `building_title`,`building`.`address` as `building_address`,`building`.`latitude` as `building_latitude` , `building`.`longitude` as `building_longitude` FROM  housing as `housing` LEFT JOIN `tower_building` as `building` ON `building`.`id` = `housing`.`building_id` LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id` LEFT JOIN `admins` ON `admins`.`id` = `housing`.`admin_id` LEFT JOIN `find_shop_collection_history` as `collection` ON  `collection`.`user_openid` = '".$openid."' AND `type` = 2 AND `collection`.`table` = 'housing' AND `collection`.`target_id` = `housing`.`id`   WHERE `housing`.`id` =  ".$id." LIMIT 1";
        return DB::select("SELECT  `housing`.*,CASE WHEN `collection`.`id` IS NOT NULL THEN 1 ELSE 0 END as `collection`,`areaCommunity`.`name` as `community_name`,`admins`.`nickName` as `admin_nickName`,`admins`.`realName` as `admin_realName`,`areaStreet`.`name` as `street_name`,`building`.`title` as `building_title`,`building`.`address` as `building_address`,`building`.`latitude` as `building_latitude` , `building`.`longitude` as `building_longitude` FROM  housing as `housing` LEFT JOIN `tower_building` as `building` ON `building`.`id` = `housing`.`building_id` LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id` LEFT JOIN `admins` ON `admins`.`id` = `housing`.`admin_id` LEFT JOIN `find_shop_collection_history` as `collection` ON  `collection`.`user_openid` = '".$openid."' AND `type` = 2 AND `collection`.`table` = 'housing' AND `collection`.`target_id` = `housing`.`id`   WHERE `housing`.`id` =  ".$id." LIMIT 1" );

    }

    //查询语句
    // SELECT  `housing`.*,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name`,`building`.`title` as `building_title`,`building`.`address` as `building_address`,`building`.`latitude` as `building_latitude` , `building`.`longitude` as `building_longitude` FROM  housing as `housing` LEFT JOIN `tower_building` as `building` ON `building`.`id` = `housing`.`building_id` LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id`   WHERE `housing`.`id` = 85
    
  
    

    public static function housingWebListRand($id = 0 , $limit = 4 ){
        // echo '  SELECT  `housing`.*,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name`,`building`.`title` as `building_title` FROM  housing as `housing` LEFT JOIN `tower_building` as `building` ON `building`.`id` = `housing`.`building_id` LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id`   WHERE `housing`.`delete_time` is null AND `housing`.`shelves` = 1    '.$whereStr.' ORDER BY `housing`.`top_time` DESC , `housing`.`sort` DESC , `housing`.`id` DESC  '.$limitStr;
        return DB::select('  SELECT `housing`.`level_des`,`housing`.`id`,`housing`.`meters_money`,`housing`.`cover_image`,`housing`.`spaceNum`,`building`.`longitude` as `building_longitude`,`building`.`latitude` as `building_latitude`,`building`.`title` as `building_title` FROM  '.self::$tableName.'   LEFT JOIN `tower_building` as `building` ON  `building`.`id` =    `housing`.`building_id` WHERE `housing`.`delete_time` is null AND `housing`.`shelves` = 1 AND `housing`.`id` != '.$id.' order by rand() limit 0,'.$limit);
    }

    public static function housingStoreList($admin_id,$limitStr){
        // echo ' SELECT `all`.*, `areaCommunity`.`name` AS `community_name`, `areaStreet`.`name` AS `street_name` FROM ( SELECT "housing" AS `type`, `housing`.`cover_image`, `housing`.`id`, `housing`.`top_time`, `housing`.`sort`, `housing`.`create_time`, `building`.`community_id`, `building`.`street_id`, `housing`.`meters_money`, `housing`.`level_des`, `building`.`title`, `housing`.`spaceNum`, `housing`.`tagList` FROM `housing` LEFT JOIN `tower_building` AS `building` ON `building`.`id` = `housing`.`building_id` WHERE `housing`.`admin_id` = '.$admin_id.' AND `housing`.`shelves` = 1 AND `housing`.`delete_time` IS NULL UNION ALL SELECT "store" AS `type`, `store`.`cover_image`, `store`.`id`, `store`.`top_time`, `store`.`sort`, `store`.`create_time`, `store`.`community_id`, `store`.`street_id`, `store`.`meters_money`, `store`.`level_des`, `store`.`title`, `store`.`spaceNum`, `store`.`tagList` FROM `store` WHERE `store`.`admin_id` = '.$admin_id.' AND `store`.`shelves` = 1 AND `store`.`delete_time` IS NULL ) AS `all` LEFT JOIN `area` AS `areaStreet` ON `areaStreet`.`id` = `all`.`street_id` LEFT JOIN `area` AS `areaCommunity` ON `areaCommunity`.`id` = `all`.`community_id` ORDER BY `all`.`top_time` DESC , `all`.`sort` DESC , `all`.`create_time` DESC  '.$limitStr;
        // echo ' SELECT `all`.*, `areaCommunity`.`name` AS `community_name`, `areaStreet`.`name` AS `street_name` FROM ( SELECT "housing" AS `type`, `housing`.`cover_image`, `housing`.`id`, `housing`.`top_time`, `housing`.`sort`, `housing`.`create_time`, `building`.`community_id`, `building`.`street_id`, `housing`.`meters_money`, `housing`.`level_des`, `building`.`title`, `housing`.`spaceNum`, `housing`.`tagList` FROM `housing` LEFT JOIN `tower_building` AS `building` ON `building`.`id` = `housing`.`building_id` WHERE `housing`.`admin_id` = '.$admin_id.' AND `housing`.`shelves` = 1 AND `housing`.`delete_time` IS NULL UNION ALL SELECT "store" AS `type`, `store`.`cover_image`, `store`.`id`, `store`.`top_time`, `store`.`sort`, `store`.`create_time`, `store`.`community_id`, `store`.`street_id`, `store`.`meters_money`, `store`.`level_des`, `store`.`title`, `store`.`spaceNum`, `store`.`tagList` FROM `store` WHERE `store`.`admin_id` = '.$admin_id.' AND `store`.`shelves` = 1 AND `store`.`delete_time` IS NULL ) AS `all` LEFT JOIN `area` AS `areaStreet` ON `areaStreet`.`id` = `all`.`street_id` LEFT JOIN `area` AS `areaCommunity` ON `areaCommunity`.`id` = `all`.`community_id` ORDER BY `all`.`top_time` DESC , `all`.`sort` DESC , `all`.`create_time` DESC  '.$limitStr;
        return DB::select( ' SELECT `all`.*, `areaCommunity`.`name` AS `community_name`, `areaStreet`.`name` AS `street_name` FROM ( SELECT "housing" AS `type`, `housing`.`cover_image`, `housing`.`id`, `housing`.`top_time`, `housing`.`sort`, `housing`.`create_time`, `building`.`community_id`, `building`.`street_id`, `housing`.`meters_money`, `housing`.`level_des`, `building`.`title`, `housing`.`spaceNum`, `housing`.`tagList` FROM `housing` LEFT JOIN `tower_building` AS `building` ON `building`.`id` = `housing`.`building_id` WHERE `housing`.`admin_id` = '.$admin_id.' AND `housing`.`shelves` = 1 AND `housing`.`delete_time` IS NULL UNION ALL SELECT "store" AS `type`, `store`.`cover_image`, `store`.`id`, `store`.`top_time`, `store`.`sort`, `store`.`create_time`, `store`.`community_id`, `store`.`street_id`, `store`.`meters_money`, `store`.`level_des`, `store`.`title`, `store`.`spaceNum`, `store`.`tagList` FROM `store` WHERE `store`.`admin_id` = '.$admin_id.' AND `store`.`shelves` = 1 AND `store`.`delete_time` IS NULL ) AS `all` LEFT JOIN `area` AS `areaStreet` ON `areaStreet`.`id` = `all`.`street_id` LEFT JOIN `area` AS `areaCommunity` ON `areaCommunity`.`id` = `all`.`community_id` ORDER BY `all`.`top_time` DESC , `all`.`sort` DESC , `all`.`create_time` DESC  '.$limitStr);

    }
}
